

import sys, os, django
 
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) #把manage.py所在目录添加到系统目录
os.environ['DJANGO_SETTINGS_MODULE'] = 'hg_survey.settings' #设置setting文件
django.setup()#初始化Django环境

import pandas as pd
from students.models import Student, Academy, MajorAndClass
from django.contrib.auth.models import User

import logging
logging.basicConfig(format='%(asctime)s %(message)s', filename='insert.log',level=logging.DEBUG)

# $todo: 删除全部student
def delete_all_student():
    pass

def delete_academy():
    pass

def delete_major_and_class():
    pass

def insert_data(filepath):
    if not filepath.endswith('xlsx'):
        raise ValueError('导入模板必须是xlsx格式')
        
    user_list = pd.read_excel(filepath)
    academy_list = user_list['学院'].unique().tolist()
    major_list = user_list['专业班级'].unique().tolist()

    # $todo: 1. 处理academy重名异常
    # $todo: 2. 判断academy是否已经在数据库中，不在需要新建
    for item in academy_list:
        academy = Academy(name=item)
        try:
            academy.save()
        except Exception:
            pass
        
    # $todo: 判断major是否已经在数据库中，不在需要新建
    for item in major_list:
        maj, _class = item[:-2], item[-2:]
        major = MajorAndClass(major=maj, _class=_class)
        try:
            major.save()
        except Exception:
            pass

    counter = 1
    for _, series in user_list.iterrows():
        user_name = series['姓名']
        _id = series['学号']
        _class = series['专业班级']
        academy = series['学院']

        academy = Academy.objects.get(name=academy)
        _class = MajorAndClass.objects.get(major=_class[:-2], _class=_class[-2:])

        # $todo: 处理重名异常
        user = User(username=_id, password="123456", is_active=True)
        user.save()
        student = Student(
                    student_id=user,
                    student_name=user_name,
                    student_class=_class,
                    student_academy=academy
                    )
        student.save()

        counter += 1
        logging.info(f'{counter}: {student} created')

        if counter > 10:
            break

def populate_academy():
    pass

def populate_major_and_class():
    pass
    

if __name__ == "__main__":
    filename = "学生导入模板.xlsx"
    insert_data(filename)
